# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import pymysql
from twisted.enterprise import adbapi


class MyspiderPipeline(object):
    def __init__(self, dbpool):
        self.dbpool = dbpool

    @classmethod
    def from_settings(cls, settings):  # 函数名固定，会被scrapy调用，直接可用settings的值
        """
        数据库建立连接
        :param settings: 配置参数
        :return: 实例化参数
        """
        adbparams = dict(
            host=settings['MYSQL_HOST'],
            port=settings['MYSQL_PORT'],
            db=settings['MYSQL_DBNAME'],
            user=settings['MYSQL_USER'],
            password=settings['MYSQL_PASSWD'],
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor,  # 指定cursor类型
            use_unicode=True,
        )

        # 连接数据池ConnectionPool，使用pymysql或者Mysqldb连接
        dbpool = adbapi.ConnectionPool('pymysql', **adbparams)
        # 返回实例化参数
        return cls(dbpool)

    def process_item(self, item, spider):
        print(item)
        # 使用twisted将MySQL插入变成异步执行。通过连接池执行具体的sql操作，返回一个对象
        query = self.dbpool.runInteraction(self.do_insert, item)  # 指定操作方法和操作数据
        # 添加异常处理
        query.addCallback(self.handle_error)  # 处理异常

        return item

    def do_insert(self, cursor, item):
        # 对数据库进行插入操作，并不需要commit，twisted会自动commit
        keys = ','.join(item.keys())
        values = ','.join(['%s'] * len(item))
        upkey = self.upkeyitem(item)
        insert_sql = "INSERT INTO {table} ({keys}) VALUES ({values}) on duplicate key update {upkey}".format(
            table='company_11467_cc_city', keys=keys, values=values, upkey=upkey)
        try:
            cursor.execute(insert_sql, tuple(item.values())*2)
        except Exception:
            pass

    def handle_error(self, failure):
        if failure:
            # 打印错误信息
            print(failure)
            pass

    def upkeyitem(self, items):
        itemstr = ''
        for item in items.keys():
            itemstr = itemstr + item + '=%s,'
        return itemstr[:-1]
